﻿USE [EvidenceManagement];
GO

SET XACT_ABORT ON
GO

BEGIN TRAN
GO

DECLARE @emptyGuid AS UNIQUEIDENTIFIER, 
        @now AS DATETIME, 
        @arbitraryEmployeeNo AS VARCHAR (24);

SET @emptyGuid = '00000000-0000-0570-0000-000000000000';

SET @now = GETDATE();

SET @arbitraryEmployeeNo = lower(CAST (replace(CAST (NEWID() AS VARCHAR (50)), '-', '') AS VARCHAR (24)));

--DIVISION 
INSERT  INTO [dbo].[Division] ([DivisionID], [CreatedDate], [CreatedBy], [ModifiedDate], [ModifiedBy], [Deleted], [Description], [SystemDescription])
VALUES                       (@emptyGuid, @now, @emptyGuid, @now, @emptyGuid, 0, 'Local System', 'LOCAL_SYSTEM');

--DESIGNATION
INSERT  INTO [dbo].[Designation] ([DesignationID], [CreatedDate], [CreatedBy], [ModifiedDate], [ModifiedBy], [Deleted], [Description], [SystemDescription])
VALUES                          (@emptyGuid, @now, @emptyGuid, @now, @emptyGuid, 0, 'System', 'SYSTEM');

--GENDER
INSERT  INTO [dbo].[Gender] ([GenderID], [CreatedDate], [CreatedBy], [ModifiedDate], [ModifiedBy], [Deleted], [Description], [SystemDescription])
VALUES                     (@emptyGuid, @now, @emptyGuid, @now, @emptyGuid, 0, 'system', 'SYSTEM_DEFAULT');

--IDENTITY TYPE
INSERT  INTO [dbo].[IdentityType] ([IdentityTypeID], [CreatedDate], [CreatedBy], [ModifiedDate], [ModifiedBy], [Deleted], [Description], [SystemDescription])
VALUES                           (@emptyGuid, @now, @emptyGuid, @now, @emptyGuid, 0, 'system', 'SYSTEM_DEFAULT');

--RACE
INSERT  INTO [dbo].[Race] ([RaceID], [CreatedDate], [CreatedBy], [ModifiedDate], [ModifiedBy], [Deleted], [Description], [SystemDescription])
VALUES                   (@emptyGuid, @now, @emptyGuid, @now, @emptyGuid, 0, 'system', 'SYSTEM_DEFAULT');

--CONTACT DETAIL
INSERT  INTO [dbo].[ContactDetail] ([ContactDetailID], [CreatedDate], [CreatedBy], [ModifiedDate], [ModifiedBy], [Deleted], [HomeNumber], [MobileNumber], [WorkNumber], [FaxNumber], [EmailAddress])
VALUES                            (@emptyGuid, @now, @emptyGuid, @now, @emptyGuid, 0, '', '', '', '', '');

--COUNTRY
INSERT  INTO [dbo].[Country] ([CountryID], [CreatedDate], [CreatedBy], [ModifiedDate], [ModifiedBy], [Deleted], [Description], [SystemDescription])
VALUES                      (@emptyGuid, @now, @emptyGuid, @now, @emptyGuid, 0, 'system', 'SYSTEM_DEFAULT');

--PROVINCE
INSERT  INTO [dbo].[Province] ([ProvinceID], [CreatedDate], [CreatedBy], [ModifiedDate], [ModifiedBy], [Deleted], [CountryID], [Description], [SystemDescription])
VALUES                       (@emptyGuid, @now, @emptyGuid, @now, @emptyGuid, 0, @emptyGuid, 'system', 'SYSTEM_DEFAULT');

--ADDRESS
INSERT  INTO [dbo].[Address] ([AddressID], [CreatedDate], [CreatedBy], [ModifiedDate], [ModifiedBy], [Deleted], [ProvinceID], [Line1], [Line2], [Suburb], [Code], [Longitude], [Latitude])
VALUES                      (@emptyGuid, @now, @emptyGuid, @now, @emptyGuid, 0, @emptyGuid, 'system', 'system', 'system', 'system', 0000000000, 0000000000);

--PERSON
INSERT  INTO [dbo].[Person] ([PersonID], [CreatedDate], [CreatedBy], [ModifiedDate], [ModifiedBy], [Deleted], [GenderID], [RaceID], [ResidentialAddressID], [ContactDetailID], [IdentityTypeID], [FirstName], [MiddleName], [Surname], [Initials], [IdentityNumber], [DateOfBirth], [Age])
VALUES                     (@emptyGuid, @now, @emptyGuid, @now, @emptyGuid, 0, @emptyGuid, @emptyGuid, @emptyGuid, @emptyGuid, @emptyGuid, 'system', '', 'local', 's', '0000000000000', @now, 0);

--EMPLOYEE
INSERT  INTO [dbo].[Employee] ([EmployeeID], [CreatedDate], [CreatedBy], [ModifiedDate], [ModifiedBy], [Deleted], [DesignationID], [DivisionID], [PersonID], [EmployeeNumber])
VALUES                       (@emptyGuid, @now, @emptyGuid, @now, @emptyGuid, 0, @emptyGuid, @emptyGuid, @emptyGuid, @arbitraryEmployeeNo);

INSERT  INTO [dbo].[SystemUser] ([SystemUserID], [CreatedDate], [CreatedBy], [ModifiedDate], [ModifiedBy], [Deleted], [EmployeeID], [UserName], [Salt], [Password], [LastLoginDate], [LastPasswordChangeDate], [NextPasswordChangeDate], [ApprovedBy], [ApprovedDate], [IsApproved], [IsLoggedOn], [IsLockedOut])
VALUES                         (@emptyGuid, @now, @emptyGuid, @now, @emptyGuid, 0, @emptyGuid, 'LOCALSYSTEM', @arbitraryEmployeeNo, @arbitraryEmployeeNo, '01 Jan 1900', '01 Jan 9999', '01 Jan 9999', @emptyGuid, '01 Jan 1900', 0, 0, 0);

COMMIT TRAN
GO